package cn.edu.scau.daoImpl;

import java.util.List;

import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.transform.Transformers;
import org.springframework.stereotype.Repository;

import cn.edu.scau.dao.DownloadDao;
import cn.edu.scau.entity.Download;

@Repository("downloadDaoImpl")
public class DownloadDaoImpl extends BaseDaoImpl<Download> implements DownloadDao {

	@Override
	public List getDownloadsByLikeTimes(int size) {

		String sql = "SELECT * FROM "
				+ "(SELECT ROW_NUMBER() OVER (ORDER BY LikeTimes DESC) AS RowNum, * "
				+ "FROM [Scauaie.com].[dbo].[Downloads] where Status = 1 ) AS sub " + "WHERE sub.RowNum BETWEEN "
				+ 1 + " AND " + size;
		Session session = getSessionFactory().openSession();
		Query query = session.createSQLQuery(sql).addEntity(Download.class);
		List list = query.list();
		session.flush();
		session.close();
		
//		String hql = "select top "+ size +" d.download_id , d.title ," + " d.resourceUrl from Download as d "
//				+ "where d.status = true order by d.likeTimes desc";
		return list;
	}

	public Long getDownloadsCountFront() {

		Object[] objs = new Object[1];
		objs[0] = true;
		List<?> list = findByHql("select count(*) from Download d where d.status =?0", objs);
		if (list != null && list.size() > 0) {
			return (Long) list.get(0);
		}
		return (long) 0;
	}

	@Override
	public List getPropertyOfDownloadFront(Integer page, Integer pageSize) {

		String sql = "SELECT * FROM "
				+ "(SELECT ROW_NUMBER() OVER (ORDER BY ID) AS RowNum, * "
				+ "FROM [Scauaie.com].[dbo].[Downloads] where Status = 1 ) AS sub " + "WHERE sub.RowNum BETWEEN "
				+ (page - 1) * pageSize + 1 + " AND " + page * pageSize;
		Session session = getSessionFactory().openSession();
		Query query = session.createSQLQuery(sql).addEntity(Download.class);
		List list = query.list();
		session.flush();
		session.close();

		return list;

	}

	@Override
	public List getPropertyOfDownloadManagement(Integer page, Integer pageSize) {

		
		String sql = "SELECT * FROM "
				+ "(SELECT ROW_NUMBER() OVER (ORDER BY ID DESC) AS RowNum , * "
				+ "FROM [Scauaie.com].[dbo].[Downloads]) AS sub " + "WHERE sub.RowNum BETWEEN "
				+ (page - 1) * pageSize + 1 + " AND " + page * pageSize;
		Session session = getSessionFactory().openSession();
		Query query = session.createSQLQuery(sql).addEntity(Download.class);
		List list = query.list();
		session.flush();
		session.close();

		return list;

	}
}
